<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML" 4.01"//EN" "http://www.w3.org/TR/html4/strict.dtd">
<!-- Created: August 20, 2001-->
<!--
$Id: RepSurvSched.php,v 1.8 2002/01/02 14:25:21 eugenem Exp eugenem $
$Header: /export/home/eugenem/public_html/ComplianceReports/RCS/RepSurvSched.php,v 1.8 2002/01/02 14:25:21 eugenem Exp eugenem $
$Log: RepSurvSched.php,v $
Revision 1.8  2002/01/02 14:25:21  eugenem
Insertion of data into temp tables was skipping 1/1 and 12/31.  Changed selection criteria to include these dates.

Revision 1.7  2001/11/02 19:09:32  eugenem
Moved HTTP_*_VARS processing to dbc.inc

Revision 1.6  2001/11/02 15:00:03  eugenem
Moved database connect statements to a separate file.

Revision 1.5  2001/10/23 20:44:20  eugenem
Made SurveyID column a link to ResolveRecommendations.php so that recommendations for that survey can be viewed.
Made Description column a link to RepMachine.php so that machine information can be viewed.

Revision 1.4  2001/10/22 19:33:36  eugenem
Display previous and current surveyIDs in the Survey Schedule table.

Revision 1.3  2001/10/02 14:51:18  eugenem
Added SurveyID column to Pending Surveys section.

Revision 1.2  2001/09/06 14:03:45  eugenem
Added table to show pending surveys.

Revision 1.1  2001/08/21 18:15:52  eugenem
Initial revision

-->

<html>
<head>
<title>Radiological Equipment Database</title>
<meta name="author" content="Eugene Mah" />
<link rev="made" href="mailto:eugenem@ix.netcom.com" />
<link rev="home" href="http://blog.imabug.net/" />
<meta name="description" content="" />
<meta name="Keywords" content="" />
<meta name="Reply-to" content="eugenem@ix.netcom.com" />
<META http-equiv="PICS-Label" content='(PICS-1.1 "http://www.classify.org/safesurf/" l r (SS~~0001))' />
<?PHP
require('functions.inc');
require('dbc.inc');

date_default_timezone_set('America/New_York');

$today = date("Ymd");
$year = date("Y");
$month = date("m");
$day = date("d");
?>
</head>

<body>
<h1>Radiological Equipment Database</h1>
<?PHP
/*
  Create temporary tables to hold the surveys from the previous year
  and this year
*/
$query = "CREATE TEMPORARY TABLE lastyear (
            MachineID int not null,
        TestDate date, 
      pSurveyID int default NULL, 
      PRIMARY KEY MachineID ( MachineID ), 
      INDEX (pSurveyID),
      INDEX (TestDate))";
$result = mysql_query($query)
  or die ("Unable to perform query: $query");
$query = "CREATE TEMPORARY TABLE thisyear (
            MachineID int not null, 
      TestDate date, 
      cSurveyID int default NULL, 
      PRIMARY KEY MachineID ( MachineID ), 
      INDEX (cSurveyID),
      INDEX (TestDate))";
$result = mysql_query($query)
  or die ("Unable to perform query: $query<br />\n" . mysql_error() . "<br />\n");
/*
  Populate the temporary tables
*/
// Select all the acceptance (TestDates.TypeID=2) or routine (TestDates.TypeID=1)
// surveys from the previous year and insert them into the lastyear table
$query = $insStr . " INTO lastyear (MachineID,TestDate,pSurveyID) 
          SELECT Survey2Machine.MachineID,TestDate,Survey2Machine.SurveyID 
      FROM Survey2Machine
      LEFT JOIN TestDates USING (SurveyID)
      WHERE TestDate BETWEEN MAKEDATE(YEAR(CURDATE())-1,1) AND MAKEDATE(YEAR(CURDATE()),1)
      AND (TestDates.TypeID=1 or TestDates.TypeID=2)
      ORDER BY TestDate DESC";
$result = mysql_query($query)
  or die ("Unable to perform query: $query<br />\n" . mysql_error() . "<br />\n");

// Select all the acceptance (TestDates.TypeID=2) or routine (TestDates.TypeID=1)
// surveys done this year and insert them into the thisyear table
$query = $insStr . " INTO thisyear (MachineID,TestDate,cSurveyID) 
          SELECT Survey2Machine.MachineID,TestDate,Survey2Machine.SurveyID 
      FROM Survey2Machine
      LEFT JOIN TestDates USING (SurveyID)
      WHERE TestDate BETWEEN MAKEDATE(YEAR(CURDATE()),1) AND MAKEDATE(YEAR(CURDATE())+1,1)
      AND (TestDates.TypeID=1 or TestDates.TypeID=2)
      ORDER BY TestDate DESC";
$result = mysql_query($query)
  or die ("Unable to perform query: $query<br />\n" . mysql_error() . "<br />\n");
  
// Fill out the thisyear table with the rest of the machines that don't have surveys done yet
// This will insert MachineIDs for all machines into the thisyear table.  Duplicates will be ignored.
$query = $insStr . " INTO thisyear (MachineID) SELECT MachineID from Machines WHERE Status=\"Active\"";
$result = mysql_query($query)
  or die ("Unable to perform query: $query<br />\n" . mysql_error() . "<br />\n");

?>

<?PHP
/*
  Count the number of machines that are active.
  This will be the number of machines that will need to be surveyed
  this year
*/
$query = "SELECT count(*) from Machines where Status=\"Active\"";
$result = mysql_query($query)
  or die("Unable to perform query: $query<br />\n" . mysql_error() . "<br />\n");
$row = mysql_fetch_row($result);
$m = $row[0];

/*
  Get a list of all machines that haven't been surveyed this year yet
*/
$query = "SELECT thisyear.MachineID,Description,lastyear.TestDate AS Previous,thisyear.TestDate AS Current
          FROM thisyear 
      LEFT JOIN lastyear USING (MachineID),Machines 
      WHERE Machines.MachineID=thisyear.MachineID 
      AND Status=\"Active\" 
      AND thisyear.TestDate IS NULL 
      ORDER BY lastyear.TestDate,Machines.MachineID";
$result = mysql_query($query)
  or die ("Unable to perform query: $query<br />\n" . mysql_error() . "<br />\n");

// Count the number of machines not surveyed yet this year
$n = mysql_num_rows($result);
echo "<h2>Surveys to be scheduled ($n/$m)</h2>\n";
echo "<p>Click a link to schedule a survey for a unit</p>\n";
/*
  Display the unsurveyed machines in a table
*/

echo "<table border=0 summary='Table of the unsurveyed machines for the current calendar year'>\n";
while ($row = mysql_fetch_array($result)) {
  echo "  <tr>\n";
  echo "    <td><a href='SurveyAdd.php?MachID=" . $row['MachineID'] . "'>" . $row['Description'] . "</a></td>\n";
  $row = mysql_fetch_array($result);
  echo "    <td><a href='SurveyAdd.php?MachID=" . $row['MachineID'] . "'>" . $row['Description'] . "</a></td>\n";
  $row = mysql_fetch_array($result);
  echo "    <td><a href='SurveyAdd.php?MachID=" . $row['MachineID'] . "'>" . $row['Description'] . "</a></td>\n";
  $row = mysql_fetch_array($result);
  echo "    <td><a href='SurveyAdd.php?MachID=" . $row['MachineID'] . "'>" . $row['Description'] . "</a></td>\n";
  $row = mysql_fetch_array($result);
  echo "    <td><a href='SurveyAdd.php?MachID=" . $row['MachineID'] . "'>" . $row['Description'] . "</a></td>\n";
  echo "  </tr>\n";
}

mysql_free_result($result);
?>
</table>
<h2>Pending surveys</h2>
<table border="0" summary="Table of the scheduled but pending surveys for the current calendar year">
  <tr>
    <th>Survey ID</th><th>Description</th><th>Date Scheduled</th><th>Accession</th><th>Survey Note</th>
  </tr>
<?PHP
/*
  Get a list of all machines with pending surveys scheduled
*/
$query = "SELECT Survey2Machine.SurveyID,Description,TestDate,Accession,TestDates.Notes 
          FROM TestDates
      LEFT JOIN Survey2Machine USING (SurveyID)
      LEFT JOIN Machines ON (Survey2Machine.MachineID=Machines.MachineID)
      WHERE TestDate >= CURDATE()";

$result = mysql_query($query)
  or die ("Unable to perform query: $query<br />\n" . mysql_error() . "<br />\n");

while ($row = mysql_fetch_array($result)) {
  echo "  <tr>\n";
  echo "    <td>" . $row['SurveyID'] . "</td>\n";
  echo "    <td>" . $row['Description'] . "</td>\n";
  echo "    <td>" . $row['TestDate'] . "</td>\n";
  echo "    <td>" . $row['Accession'] . "</td>\n";
  echo "    <td>" . $row['Notes'] . "</td>\n";
  echo "  </tr>\n";
}

mysql_free_result($result);
?>
</table>

<h2>Survey Schedule</h2>

<table border="1" summary="Table of all the machines, dates and survey IDs of the previous and current surveys">
  <tr>
    <th>ID</th><th>Description</th><th>Previous</th><th>Prev SurveyID</th><th>Current</th><th>Curr SurveyID</th><th>Recs</th><th>Recs Resolved</th>
  </tr>
<?PHP
// Get a list of all the machines and their previous/current test dates
$query = "SELECT Machines.MachineID,Description,lastyear.TestDate AS Previous,thisyear.TestDate AS Current,pSurveyID,cSurveyID 
          FROM thisyear 
      LEFT JOIN lastyear USING (MachineID),Machines 
      WHERE Machines.MachineID=thisyear.MachineID AND Status=\"Active\" 
      ORDER BY lastyear.TestDate,Machines.MachineID";
$result = mysql_query($query)
  or die ("Unable to perform query: $query<br />\n" . mysql_error() . "<br />\n");

  // Spit the results out into a table
while ($row = mysql_fetch_array($result)) {
  echo "  <tr>\n";
  echo "    <td>" . $row['MachineID'] . "</td>\n";
  // Take user to the Machine report if they click on the Machine description
  echo "    <td><a href=\"RepMachine.php?MachID=" . $row['MachineID'] . "\"> " . $row['Description'] . "</a></td>\n";
  if (empty($row['Previous'])) {
    echo "    <td colspan=2 align=center><b>No survey</b></td>\n";
  }
  else {
    echo "    <td>" . $row['Previous'] . "</td>\n";
    echo "    <td align=center>";
    // Take user to the report recommendations if they click on the survey ID
    echo "<a href=\"SurveyResRec.php?SurveyID=" . $row['pSurveyID'] . "\">" . $row['pSurveyID'] . "</a></td>\n";
  }
  if (empty($row['Current'])) {
    echo "    <td colspan=2 align=center><b>Not Done Yet</b></td><td>N/A</td><td>N/A</td>\n";
  }
  else {
    echo "    <td>" . $row['Current'] . "</td>\n";
    echo "    <td align=center>" . "<a href=\"SurveyResRec.php?SurveyID=" . $row['cSurveyID'] . "\">" . $row['cSurveyID'] . "</a></td>\n";

    // Count how many survey recommendations there are for the current survey
    $rec_query = "SELECT count(*) AS nRec FROM Recommendations WHERE SurveyID=" . $row['cSurveyID'];
    $rec_result = mysql_query($rec_query);
    $rec_count = mysql_fetch_array($rec_result);
    if ($rec_count['nRec']==0) {
      // No recommendations
      echo "<td> None</td><td>None</td>\n";
    }
    else {
      // There are recommendations
      // echo "<td>" . $rec_count['nRec'] . " Yes</td>\n";
      echo "<td>Yes</td>\n";
      // Count number of survey recommendations resolved. If <>0, Recs Resolved is No.
      $resRec_query = "SELECT count(*) AS resRec FROM Recommendations WHERE SurveyID=" . $row['cSurveyID'] . " AND Resolved=1";
      $resRec_result = mysql_query($resRec_query);
      $resRec_count = mysql_fetch_array($resRec_result);
      if ($resRec_count['resRec']<$rec_count['nRec']) {
        // Count of resolved recommendations is less than total # of recommendations
        // Still some outstanding recommendations
        // echo "<td>" . $resRec_count['resRec'] . "/" . $rec_count['nRec'] . " No</td>\n";
        echo "<td><strong>No</strong></td>\n";
      }
      elseif ($resRec_count['resRec'] == $rec_count['nRec']) {
        // Count of resolved recommendations matches count of total recommendations.
        // Everything resolved
        // echo "<td>" . $resRec_count['resRec'] . "/" . $rec_count['nRec'] . " Yes</td>\n";
        echo "<td>Yes</td>\n";
      }
      mysql_free_result($resRec_result);
    }
    mysql_free_result($rec_result);
  }
  echo "  <tr>\n";
}
mysql_free_result($result);
?>
</table>
<p><a href="index.html" target="_top" >Main menu</a></p>
</body>
</html>
